﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using LibHuman;

namespace Human
{
    public partial class frm_Dsbinhbauthidua : Form
    {
        AccessData mDb = new AccessData();
        Language lan = new Language();
        string user_dn="",s_bophan="",sql = "", m_user = "", madv = "", msg = "", ReportName = "", s_mmyy = "";
        DataSet ds = new DataSet();
        public frm_Dsbinhbauthidua(string s_makp,string s_userdn)
        {
            InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
        }
        
        private void frm_Dsbinhbauthidua_Load(object sender, EventArgs e)
        {   
            m_user=mDb.user;            
            load_bophan();            
            cb_nam.Text = DateTime.Now.Year.ToString();
        }      
       
        private void load_bophan()
        {            
            cb_bophan.DisplayMember = "tenbp";
            cb_bophan.ValueMember = "madv";
            cb_bophan.DataSource = mDb.get_bophan().Tables[0];            
        }

        private void cb_bophan_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.ActiveControl==cb_bophan)
            {
                if (cb_bophan.SelectedIndex != -1)
                {
                    madv = cb_bophan.SelectedValue.ToString().Trim();
                }
            }
        }

        private void chk_donvi_CheckedChanged(object sender, EventArgs e)
        {
            if (chk_donvi.Checked == true)
            {
                cb_bophan.SelectedIndex = -1;
                cb_bophan.Enabled = false;
            }
            else
            {
                cb_bophan.SelectedIndex = 0;
                cb_bophan.Enabled = true;
            }
        }

        private void btn_thoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        private void print(int prn)
        {
            string sql1 = "", tam = "";
            sql = "select llnv.manv,trim(llnv.ho)||' '||trim(llnv.ten) as hoten ,dmcv.diengiai as chucvu,bophan.sothutu as stt_bp ";
            sql += " ,ctdaotao.tenctdt as trinhdo,bophan.tenbp as khoaphong,bophan.madv,kt.ghichu as ghichu";
            sql += " ,case when llnv.phai='Nam' then to_char(llnv.ngaysinh,'dd/mm/yyyy') else '' end as ngaysinhnam ";
            sql += " ,case when llnv.phai<>'Nam' then to_char(llnv.ngaysinh,'dd/mm/yyyy')  else '' end as ngaysinhnu "; 
            sql += " ,case when kt.giaykhen=true then 1 else 0 end  as giaykhen";
            sql += " ,case when kt.bangkhen=true then 1 else 0 end  as bangkhen";
            sql += " ,case when kt.so_kt=true then 1 else 0 end  as toanquoc";
            sql += " ,case when kt.ub_kt=true then 1 else 0 end  as tinh";
            sql += " ,case when kt.cs_kt=true then 1 else 0 end  as coso ";
            sql += " ,case when kt.canam=true then 1 else 0 end  as ldtt ";
            sql += " ,case when kt.cstd_ct=true then 1 else 0 end  as cstd_ct ";
            sql += " ,case when kt.cstd_tq=true then 1 else 0 end  as cstd_tq ";
            sql += " ,case when kt.bo_kt=true then 1 else 0 end  as bo_kt ";
            sql += " ,case when kt.cp_kt=true then 1 else 0 end  as cp_kt ";
            sql += " ,case when kt.hc_h1=true then 1 else 0 end  as hc_h1 ";
            sql += " ,case when kt.hc_h2=true then 1 else 0 end  as hc_h2 ";
            sql += " ,case when kt.hc_h3=true then 1 else 0 end  as hc_h3 ";

            tam = " from " + mDb.user + ".hopdong inner join " + mDb.user + ".llnv on hopdong.manv=llnv.manv ";
            tam += " left join " + mDb.user + ".bophan on bophan.madv = llnv.madv"; 
            tam += " left join " + mDb.user + ".dmcv  on hopdong.macv=dmcv.macv ";
            tam += " left join " + mDb.user + ".ctdaotao on llnv.nnghiep = ctdaotao.mactdt ";     
            tam += " left join " + mDb.user + ".khenthuong kt on llnv.manv=kt.manv and kt.nam= " + cb_nam.Text;
            tam += " inner join " + mDb.user + ".max_lan on hopdong.manv=max_lan.manv ";
            tam += " where (hopdong.ngaynghi is null or hopdong.ngaynghi>to_timestamp('" + cb_nam.Text + "-12-31','yyyy-mm-dd')) ";
            tam += " and hopdong.ngaydenct < to_timestamp('" + cb_nam.Text + "-12-31','yyyy-mm-dd') ";
            if (!chk_donvi.Checked)
                if (cb_bophan.SelectedIndex != -1)
                {
                    tam += " and bophan.madv='" + cb_bophan.SelectedValue.ToString() + "'";
                }
            tam += " and hopdong.lan=max_lan.lan";
            sql += tam;
            sql +=  " order by bophan.sothutu,llnv.manv ";  
            sql1 =  " select sum(case when kt.canam=true then 1 else 0 end) as dat_canam,";
            sql1 += " sum(case when kt.so_kt=true then 1 else 0 end) as dat_so_kt,";
            sql1 += " sum(case when kt.cs_kt=true then 1 else 0 end) as dat_cs_kt,";
            sql1 += " sum(case when kt.ub_kt=true then 1 else 0 end) as dat_ub_kt ";
            sql1 += tam;
            s_mmyy = DateTime.Now.Month.ToString().PadLeft(2, '0') + cb_nam.Text.Trim().Substring(2, 2);
            ds = mDb.get_data(sql,sql1);
            ds.WriteXml("..\\xml\\h_dsbinhbauthidua.xml", XmlWriteMode.WriteSchema);
            if (ds.Tables[0].Rows.Count > 0)
            {
                frmReport frm = new frmReport(mDb, ds, lan.Change_language_MessageText("DANH SÁCH THI ĐUA KHEN THƯỞNG NĂM ") + cb_nam.Text, "h_dsbinhbauthidua.rpt",user_dn);
                frm.ShowDialog();
            }
            else
                MessageBox.Show(lan.Change_language_MessageText("Không có dữ liệu "),lan.Change_language_MessageText( "Thông Báo"));
         }

        private void btn_hienthi_Click(object sender, EventArgs e)
        {
            try
            {
                print(1);
            }
            catch { }
        }

        private void cb_nam_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}